Duncan Mackenzie
March 2002
Most applications created using Visual Basic are database driven; meaning that they exist to enter, edit and browse information that is stored into some form of database. With that in mind, a useful learning exercise for a new Visual Basic .NET programmer (even those coming from Visual Basic 6.0) is to create a relatively simple database driven application. Chapters 11 and 12 of Teach Yourself Visual Basic .NET in 21 Days cover a variety of topics relating to data access, providing the technical background to programming your own database driven applications. As an example of a database driven application, those two chapters introduce and discuss a sample application designed to manage your CD collection. This application is discussed but never built, making it a perfect example for use in the article. For those of you who haven’t read the book, Part 1 of this article will briefly cover some of the design elements of this application before moving on to implementation.
The intention of this system is to manage a CD collection, a topic chosen because it is simple enough to build in a short period of time, but complex enough that there are an enormous number of additional features you could choose to add if desired. Now, you may not have a CD collection that you wish to manage on the computer, but keep in mind that this is a sample to illustrate a database driven application, and the specific example I have chosen is just one possible type of application you could build.
The first step, before we start coding or even looking at databases and Windows Forms, is to determine what the application is supposed to do. The goal and target functionality of an application are known as the application Requirements, and they form the basis of all our future design decisions. For this CD Library, the base requirements are as follows:
Note The requirements have been numbered to make it easier to refer back to them at a later date. By directly linking requirements into your future design decisions, it is more likely that you will build what was requested.
The requirements documentation for a “real” system should be much more detailed, but this gives me enough information to start designing the system. With these requirements completed, the next step is to determine the logical design of the system, still not working with code or specific technology but beginning to specify architectural details such as the location of the data for a system, and the intended communication between the various components. Now, to come up with a logical design for this system, I had to make a few more decisions that what is indicated by the requirements. Some of these additional design decisions may seem obvious, especially in the context of this sample application, but it is a good idea to document any conscious decision that affects the design.
Taking all of the above requirements into consideration, the following logical design is one possible way in which this application could be implemented.
Figure 1. Diagram of basic design
As this is a simple database application, almost all the major design decisions involve the placement of the data and the means by which the application will access it. In this case, I have decided that the key goal to my design will be the abstraction of the data layer from the user interface. This is not a necessity and it is generally only a common design goal in larger (“enterprise”) applications, but it can apply to any size of application so using it in this system is a great way to learn about the concept before working on a larger project. One of the most common reasons to abstract the data access layer of an application is to allow the details of the data storage and retrieval technology to change (from MSDE to SQL Server, or from a local database to using Web Services to retrieve data) with a minimum of change to the other portions of your application. To make this as simple as possible, I have chosen to divide my data access into two separate layers, one that will be called directly by the user interface (the Data Access Façade in the diagram above) and then the actual data access work will be done in the Data Access Layer. By using this intermediate layer (the façade), any future change in data access will require the minimum amount of work to implement, as only a new version of the façade assembly (that supports the same interfaces as the existing one) would be required. In the first iteration of this system, everything will be occurring on the local machine, so all the components will be referenced as local libraries.
As a next step in my design, I list and describe the data access functions (such as “GetAllDiscs”) I feel my application is going to need, which will then allow me to start building the data access components. My initial list is a “best guess” based on reading the requirements, but I expect to run into some additional needs as I begin programming the actual application:
Data Retrieval
Data Updating/Adding/Deleting
All of these key functions will be implemented as stored procedures in the database (using MSDE, the free database engine available from Microsoft), and will be called by the code in the Data Access Layer. The user interface code will not be working directly with these functions though, as it will be going through the data access façade, which will provide an object-orientated view of the data.
A general structure for the data can be determined just from reading the requirements (earlier in this article) and could be turned into a variety of different database models. One particular model is shown in the diagram below and will be used for this example.
Figure 2. Simple Data Model
For your own interest, I am including another, more complex, data model that could have been used. The key difference between these two is flexibility; where the first model only allows for one artist per Album, for example, the second allows any number of artists to be associated with each Song, and therefore any number of Artists can be linked to a single Album. The tradeoff for this flexibility would be a more complicated programming model and more work required to create the data access functionality.
Figure 3. Complex Data Model
In the case of a CD Library application, I am dealing with commonly understood data and concepts, so the decision between these two data models can be made based on my own personal opinion. In a ‘real world’ system, data relationships such as Album to Artist would have to be researched and then explicitly stated as part of the design or requirements documentation. This is an example system, intended to illustrate several basic concepts, so the simple model will be used to avoid unnecessary complexity. Of course, there are not only two choices, a data model could be designed that is somewhere between these two possibilities or completely different.
In the next part of this project, I will create the actual physical database (using MSDE) and load it with sample data. Then, the data access components will be developed, covering both the communication with MSDE and the data façade that will be used by the Windows Forms code. For now, if you have any comments or questions on this project as it has developed so far, please email me at duncan@duncanmackenzie.net